package com.pignest.hotel.dao.hibernate;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.pignest.framework.dao.BaseHibernateDao;
import com.pignest.hotel.beans.User;
import com.pignest.hotel.beans.Role;
import com.pignest.hotel.beans.UserRole;
import com.pignest.hotel.dao.RoleDao;

@SuppressWarnings("unchecked")
public class RoleHibernateDao extends BaseHibernateDao implements RoleDao {
		
	public void removeUserFromRole(final Role role, final String[] userIds) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				String hql = "delete from com.pignest.hotel.beans.UserRole  where  role =:role and user in (:user)";
				Query q = getSession().createQuery(hql);
				q.setParameter("role", role.getId());
				q.setParameterList("user", userIds);
				q.executeUpdate();

				return null;
			}
		});
	}
	
	
	//得到角色列表
	public List<Role> listRoles(final String sortItem,final boolean asc,
			final int pageNumber,final  int pageSize, final String keyword) {
		
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				
				String hql = "from com.pignest.hotel.beans.Role as role where ";
				
				//排序字段及方式
				String sortString = " order by ";
				if (sortItem == null) {
					sortString += "role.name asc";
				} else {
					sortString += "role." + sortItem + (asc ? " asc" : " desc");
				}

				//如果查询关键字不为空，则按关键字来查询。
				if(StringUtils.isNotEmpty(keyword)){
					hql += " ( role.name like :keyword or role.description like :keyword)";
				}
				hql = hql.trim();
				//hql 语句
				if (hql.endsWith("and")) {
					hql = hql.substring(0, hql.length() - 3);
				}
				//加上排序的SQL
				hql += sortString;
				
				Query q = getSession().createQuery(hql);
				//给查询关键字赋值
				if (StringUtils.isNotEmpty(keyword)){
					q.setParameter("keyword", "%"+keyword+"%");
				}

				q.setFirstResult((pageNumber - 1) * pageSize);
				q.setMaxResults(pageSize);
				return q.list();
			}
		});
	}
	
	public Role getRoleByName(String roleName) {
		Collection list = getHibernateTemplate().find(
				"from com.pignest.hotel.beans.Role as role where role.name=?",
				roleName);
		if (list.size() > 0)
			return (Role) list.iterator().next();
		return null;
	}

	public List<User> getUsersByRole(final Role role) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String hql = "select user from com.pignest.hotel.beans.User as user,com.pignest.hotel.beans.Role as role, com.pignest.hotel.beans.UserRole as uhr where user=uhr.user and uhr.role=role and role=?";
				return getHibernateTemplate().find(hql, role);
			}
		});
	}
	
	public List<Role> getRolesByUser(final User user) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String hql = "select role from com.pignest.hotel.beans.User as user,com.pignest.hotel.beans.Role as role, com.pignest.hotel.beans.UserRole as uhr where user=uhr.user and uhr.role=role and user=?";
				return getHibernateTemplate().find(hql, user);
			}
		});
	}

	public void addUserRole(User user, Role role) {
		Collection list = getHibernateTemplate(). find("from com.pignest.hotel.beans.UserRole uhr where uhr.user=? and uhr.role=?",new Object[]{user.getId(),role.getId()});
		if(list.size() == 0){
			getHibernateTemplate().save(new UserRole(user, role));
		}
	}

	public void addUserRoles(User user, List<String> roleIds) {
		ArrayList<UserRole> userHasRoles = new ArrayList<UserRole>();
		for (String roleId : roleIds) {
			Role role = get(Role.class, roleId);
			userHasRoles.add(new UserRole(user, role));
		}
//		for (int i =0, j=roleIds.size();i<j;i++) {
//			Role role = get(Role.class, roleIds.get(i));
//			Collection list = getHibernateTemplate().find("from com.pignest.hotel.beans.UserRole uhr where uhr.user=? and uhr.role=?",new Object[]{user, role});
//			if(list.size() == 0){
//				userHasRoles.add(new UserRole(user, role));
//			}
//		}
		getHibernateTemplate().saveOrUpdateAll(userHasRoles);
	}

	public void addUserRoles(String[] userIds, Role role) {
		Collection<UserRole> userHasRoles = new ArrayList<UserRole>();
		for (int i=0;i<userIds.length;i++) {
			User user = get(User.class, userIds[i]);
			Collection list = getHibernateTemplate().find("from com.pignest.hotel.beans.UserRole uhr where uhr.user=? and uhr.role=?",new Object[]{user, role});
			if(list.size() == 0){
				userHasRoles.add(new UserRole(user, role));
			}
		}
		getHibernateTemplate().saveOrUpdateAll(userHasRoles);
	}

	public void deleteUserRoles(final String[] ids) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				String hql = "delete from com.pignest.hotel.beans.UserRole where id in (:userRoleIds)";
//				StringBuffer buf = new StringBuffer();
//				buf.append(hql);
//				buf.append("(");
//				for (int i = 0; i < ids.length; i++) {
//					buf.append(ids[i]).append(",");
//				}
//				buf.deleteCharAt(buf.length()-1);
//				buf.append(")");
				
				Query q = getSession().createQuery(hql);
				q.setParameterList("userRoleIds", ids);
				q.executeUpdate();
				return null;
			}
		});
	}
	
	public void clearUserRoles(final User user) {
		try {
			Collection objs = getHibernateTemplate().executeFind(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							String hql = "select uhr from com.pignest.hotel.beans.User as user,com.pignest.hotel.beans.Role as role, com.pignest.hotel.beans.UserRole as uhr where user=uhr.user and uhr.role=role and user=?";
							return getHibernateTemplate().find(hql, user);
						}
					});

			getHibernateTemplate().deleteAll(objs);
			return;
		} catch (Throwable e) {
			e.printStackTrace();
		}

		getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String hql = "delete com.pignest.hotel.beans.UserRole where user=:user";
				Query q = session.createQuery(hql);
				q.setParameter("user", user);
				q.executeUpdate();
				return null;
			}
		});
	}

	public void clearUserRoles(final Role role) {
		Collection objs = getHibernateTemplate().executeFind(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						return getHibernateTemplate()
								.find("select uhr from com.pignest.hotel.beans.User as user,com.pignest.hotel.beans.Role as role, com.pignest.hotel.beans.UserRole as uhr where user=uhr.user.id and uhr.role=role and role=?",
										role);
					}
				});
		getHibernateTemplate().deleteAll(objs);

		// getHibernateTemplate().executeFind(new HibernateCallback() {
		// public Object doInHibernate(Session session)
		// throws HibernateException, SQLException {
		// String hql = "delete com.pignest.hotel.beans.UserRole as uhr where
		// role.roleId = :roleId";
		// Query q = session.createQuery(hql);
		// q.setParameter("roleId",new Long(role.getId()));
		// q.executeUpdate();
		// return null;
		// }});

	}
	
//	public boolean hasDuplicateRoleName(Role role){
//		Collection list = getHibernateTemplate().find("select count(*) from com.pignest.hotel.beans.Role as role where role.id!=? and role.name=?",new Object[]{role.getId(),role.getName()});
//		Integer count = (Integer)list.iterator().next();
//		if(count.intValue() != 0){
//			return true;
//		}else {
//			return false;
//		}
//	}
	
}
